
package saeildamil.ch21.dbconn;


import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;


public class JdbcEx01 {
	public static void main(String[] args) {
		Connection con = null;
		try {
			con = dbCon();
			JOptionPane.showMessageDialog(null, "데이터베이스 연결 성공"); 
		    selectDao(con);
		    
			//selectwhereDAO(con);
		    insertDAO(con);
		} finally{
			try {con.close();}catch(Exception e){ }
		}
	}
	
	private static void insertDAO(Connection con) {
		Statement stmt = null;
		String name = JOptionPane.showInputDialog("상품이름 입력").trim();
		int price = Integer.parseInt(JOptionPane.showInputDialog("가격입력").trim());
		String maker = JOptionPane.showInputDialog("제조사 입력").trim();
		
		String sql = "insert into goodsinfo (name,price,maker) values ('"+name+"'," +price+ ",'" +maker+"')"; 
		JOptionPane.showMessageDialog(null, sql);
		
		try {
			stmt = con.createStatement();
			int result = stmt.executeUpdate(sql);
			JOptionPane.showMessageDialog(null, "삽입결과: " + result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static void selectwhereDAO(Connection con) {
		Statement stmt = null;
		ResultSet rs = null;
		int code = 0;
		int price = 0;
		String name = null;
		String maker = null;
		GoodsInfoDTO obj = null;
		
		try {
			stmt = con.createStatement();
			String searchName = JOptionPane.showInputDialog("검색하고자 하는 이름을 입력");
			String sql = "select code, name, price, maker from goodsinfo where name = '"+ searchName +"'";
			JOptionPane.showMessageDialog(null, sql);
			
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				code = rs.getInt(1);
				name = rs.getString("name");
				price = rs.getInt("price");
				maker = rs.getString(4);
				
				JOptionPane.showMessageDialog(null, "code: " + code + "name: " +name + "price: " + price + "maker : " + maker );
			}

			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch lock
					e.printStackTrace();
				}
			
		}
		
	}

	private static void selectDao(Connection con) {
		Statement stmt = null;
		ResultSet rs = null;
		int code = 0;
		int price = 0;
		String name = null;
		String maker = null;
		GoodsInfoDTO obj = null;
		ArrayList<GoodsInfoDTO> goodsList = new ArrayList<>();
		String sql;
		try {		
			stmt = con.createStatement();
			int num = Integer.parseInt(JOptionPane.showInputDialog("1.결과검색 2.조건검색"));
			if(num==1){
				sql = "select code,name,price,maker from goodsinfo;";
			}else{
				String searchName = JOptionPane.showInputDialog("검색하고자 하는 이름을 입력");
				sql = "select code, name, price, maker from goodsinfo where name = '"+ searchName +"'";
				JOptionPane.showMessageDialog(null, sql);
			}
			rs = stmt.executeQuery(sql);
			

			while(rs.next()){
				code = rs.getInt(1);
				name = rs.getString("name");
				price = rs.getInt("price");
				maker = rs.getString(4);
				obj = new GoodsInfoDTO(code, name, price, maker);
				goodsList.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try{
				rs.close();
				stmt.close();
			}catch (Exception e){ }
		}

		for(GoodsInfoDTO goods:goodsList){
			System.out.println(goods);
		}
	}
	private static Connection dbCon(){
		Connection conn = null;
		BufferedReader bis = null;
		File file = new File("dbinfo.con");
		try {
			bis = new BufferedReader(new FileReader(file));
			String className = bis.readLine();
			String url = bis.readLine();
			String user = bis.readLine();
			String password = bis.readLine();
			Class.forName(className);
			conn = DriverManager.getConnection(url, user, password);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			try{bis.close();}catch(Exception e){}
		}
		return conn;
	}
}
